package com.cindata

import grails.transaction.Transactional
import org.springframework.security.access.annotation.Secured

import static org.springframework.http.HttpStatus.*

@Secured(['isAuthenticated()'])
@Transactional
class UserCostController
{

    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]

    def dataSource

    // 费用统计功能
    def userCostStatistics()
    {
        def max = params.max ?: 10
        def offset = params.offset ?: 0

        def db = new groovy.sql.Sql(dataSource)
        def sql = "select l.id as id, l.full_name as fullname, c.name as fees, b.name as buildingtype, count(CASE WHEN d.name = '成功' then 1 else null end) as successcount, count(CASE WHEN d.name = '失败' then 1 else null end) as failedcount, count(CASE WHEN d.name = '执行中' then 1 else null end) as pendingcount, sum(a.amount) as totalprice, (select unit_price from fees_detail where id = a.fees_detail_id) as unitprice from user_cost as a  LEFT JOIN data_dict as b on a.building_type_id = b.id LEFT JOIN fees as c on a.fees_id = c.id LEFT JOIN login_user as l on l.id = a.user_id LEFT JOIN data_dict as d on a.status_id = d.id LEFT JOIN fees_detail as f on f.id = a.fees_detail_id GROUP BY l.full_name, c.name, b.name, a.fees_detail_id, l.id ORDER BY l.full_name, c.name, b.name desc"
        def countList = db.rows(sql).asList()

        sql += " OFFSET ${offset} LIMIT ${max}"
        def userCostStatisticsList = db.rows(sql).asList()

        db.close()

        respond new UserCost(), model: [userCostStatisticsListCount: countList?.size(), userCostStatisticsList: userCostStatisticsList, params: params]
    }

    def searchUserCostStatistics()
    {

        def max = params.max ?: 10
        def offset = params.offset ?: 0

        String user = params["user"]
        String fees = params["fees"]
        String buildingType = params["buildingType"]
        String finishAtStart = params["finishAtStart"]
        String finishAtEnd = params["finishAtEnd"]

        def sql = "select l.id, l.full_name as fullname, c.name as fees, b.name as buildingtype, count(CASE WHEN d.name = '成功' then 1 else null end) as successcount, count(CASE WHEN d.name = '失败' then 1 else null end) as failedcount, count(CASE WHEN d.name = '执行中' then 1 else null end) as pendingcount, sum(a.amount) as totalprice, (select unit_price from fees_detail where id = a.fees_detail_id) as unitprice from user_cost as a  LEFT JOIN data_dict as b on a.building_type_id = b.id LEFT JOIN fees as c on a.fees_id = c.id LEFT JOIN login_user as l on l.id = a.user_id LEFT JOIN data_dict as d on a.status_id = d.id LEFT JOIN fees_detail as f on f.id = a.fees_detail_id where 1=1"
        if (user)
        {
            sql += " and l.full_name = '${user}'"
        }
        if (fees)
        {
            sql += " and c.name = '${fees}'"
        }
        if (buildingType)
        {
            sql += " and b.name = '${buildingType}'"
        }
        if (finishAtStart && finishAtStart != '')
        {
            def dates = finishAtStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(a.finish_at,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (finishAtEnd && finishAtEnd != '')
        {
            def datee = finishAtEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(a.finish_at,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        sql += " GROUP BY l.full_name, c.name, b.name, a.fees_detail_id, l.id ORDER BY l.full_name, c.name, b.name desc"
        def countSql = sql
        sql += " OFFSET ${offset} LIMIT ${max}"

        def db = new groovy.sql.Sql(dataSource)
        def userCostStatisticsList = db.rows(sql).asList()
        def counts = db.rows(countSql).asList()

        db.close()

        respond new UserCost(), model: [userCostStatisticsListCount: counts?.size(), userCostStatisticsList: userCostStatisticsList, params: params], view: 'userCostStatistics'
    }

    // 费用明细
    def index(Integer max)
    {

        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        params.offset = params.offset ? params.offset.toInteger() : 0

        def user = params['user']
        def fees = params['fees']
        def buildingType = params['buildingType']
        def finishAtStart = params['finishAtStart']
        def finishAtEnd = params['finishAtEnd']
        def sql = "from UserCost where 1=1"
        if (user)
        {
            sql += " and user.id = ${user}"
        }
        if (fees)
        {
            sql += " and fees.name = '${fees}'"
        }
        if (buildingType)
        {
            sql += " and buildingType.name = '${buildingType}'"
        }
        if (finishAtStart && finishAtStart != '')
        {
            def dates = finishAtStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(finishAt,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (finishAtEnd && finishAtEnd != '')
        {
            def datee = finishAtEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(finishAt,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }

        sql += " order by finishAt desc"

        println sql

        def userCostList = UserCost.findAll(sql, [max: params.max, offset: params.offset])
        def userCostList1 = UserCost.findAll(sql)
        respond userCostList, model: [userCostCount: userCostList1?.size(), params: params]
    }

    def show(UserCost userCost)
    {
        respond userCost
    }

    def create()
    {
        respond new UserCost(params)
    }

    @Transactional
    def save(UserCost userCost)
    {
        if (userCost == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        if (userCost.hasErrors())
        {
            transactionStatus.setRollbackOnly()
            respond userCost.errors, view: 'create'
            return
        }

        userCost.save flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.created.message', args: [message(code: 'userCost.label', default: 'UserCost'), userCost.id])
                redirect userCost
            }
            '*' { respond userCost, [status: CREATED] }
        }
    }

    def edit(UserCost userCost)
    {
        respond userCost
    }

    @Transactional
    def update(UserCost userCost)
    {
        if (userCost == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        if (userCost.hasErrors())
        {
            transactionStatus.setRollbackOnly()
            respond userCost.errors, view: 'edit'
            return
        }

        userCost.save flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.updated.message', args: [message(code: 'userCost.label', default: 'UserCost'), userCost.id])
                redirect userCost
            }
            '*' { respond userCost, [status: OK] }
        }
    }

    @Transactional
    def delete(UserCost userCost)
    {

        if (userCost == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        userCost.delete flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.deleted.message', args: [message(code: 'userCost.label', default: 'UserCost'), userCost.id])
                redirect action: "index", method: "GET"
            }
            '*' { render status: NO_CONTENT }
        }
    }

    protected void notFound()
    {
        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.not.found.message', args: [message(code: 'userCost.label', default: 'UserCost'), params.id])
                redirect action: "index", method: "GET"
            }
            '*' { render status: NOT_FOUND }
        }
    }

    def searchUserCost()
    {
        String user = params["user"]
        String fees = params["fees"]
        String buildingType = params["buildingType"]
        String status = params["status"]
        String finishAtStart = params["finishAtStart"]
        String finishAtEnd = params["finishAtEnd"]
        String description = params["description"]

        String sql = "from UserCost as u where 1=1 "
        if (user)
        {
            sql += " and u.user.id = '${user}'"
        }
        if (fees)
        {
            sql += " and u.fees.name = '${fees}'"
        }
        if (buildingType)
        {
            sql += " and u.buildingType.name = '${buildingType}'"
        }
        if (status)
        {
            sql += " and u.status.name = '${status}'"
        }
        if (finishAtStart && finishAtStart != '')
        {
            def dates = finishAtStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(u.finishAt,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (finishAtEnd && finishAtEnd != '')
        {
            def datee = finishAtEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(u.finishAt,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (description)
        {
            sql += " and u.description like '%${description}%'"
        }
        String countSql = "select count(u.id) " + sql
        sql += " order by finishAt desc"

        def list = UserCost.findAll(sql, [max: params.max ?: 10, offset: params.offset ?: 0])
        def count = UserCost.executeQuery(countSql)

        respond list, model: [userCostCount: count[0], params: params], view: 'index'
    }
}
